<!DOCTYPE html><html lang="zh-Hans" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no"><title>MySQL | DreamcatcherDENG 的个人博客</title><meta name="author" content="dreamcatcherdeng"><meta name="copyright" content="dreamcatcherdeng"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="referrer" content="no-referrer"><meta name="description" content="本文介绍了MySQL的基本知识点，收录了SQL学习和练习网站，收录了学习MySQL过程中常见的问题及解决办法">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL">
<meta property="og:url" content="https://dreamcatcherdeng.github.io/personal-blog/2022/07/13/MySQL/index.html">
<meta property="og:site_name" content="DreamcatcherDENG 的个人博客">
<meta property="og:description" content="本文介绍了MySQL的基本知识点，收录了SQL学习和练习网站，收录了学习MySQL过程中常见的问题及解决办法">
<meta property="og:locale">
<meta property="og:image" content="https://gitee.com/DreamcatcherDENG/drawing-bed/raw/master/img/wallhaven-e7ek7k.jpg">
<meta property="article:published_time" content="2022-07-13T09:38:00.957Z">
<meta property="article:modified_time" content="2022-07-16T16:23:35.742Z">
<meta property="article:author" content="dreamcatcherdeng">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://gitee.com/DreamcatcherDENG/drawing-bed/raw/master/img/wallhaven-e7ek7k.jpg"><link rel="shortcut icon" href="/personal-blog/img/favicon.png"><link rel="canonical" href="https://dreamcatcherdeng.github.io/personal-blog/2022/07/13/MySQL/"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/personal-blog/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@6/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = { 
  root: '/personal-blog/',
  algolia: undefined,
  localSearch: {"path":"search.xml","languages":{"hits_empty":"We didn't find any results for the search: ${query}"}},
  translate: undefined,
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
  copy: {
    success: 'Copy successfully',
    error: 'Copy error',
    noSupport: 'The browser does not support'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '',
  date_suffix: {
    just: 'Just',
    min: 'minutes ago',
    hour: 'hours ago',
    day: 'days ago',
    month: 'months ago'
  },
  copyright: undefined,
  lightbox: 'fancybox',
  Snackbar: undefined,
  source: {
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery@2/dist/fjGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery@2/dist/fjGallery.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isAnchor: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
  title: 'MySQL',
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2022-07-17 00:23:35'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const detectApple = () => {
      if(/iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
        document.documentElement.classList.add('apple')
      }
    }
    detectApple()
    })(window)</script><!-- hexo injector head_end start -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/katex@0.12.0/dist/katex.min.css">

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/hexo-math@4.0.0/dist/style.css">
<!-- hexo injector head_end end --><meta name="generator" content="Hexo 5.4.2"><link rel="alternate" href="/personal-blog/atom.xml" title="DreamcatcherDENG 的个人博客" type="application/atom+xml">
</head><body><div id="web_bg"></div><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src="https://n.sinaimg.cn/sinakd10100/452/w1080h972/20200416/93dc-iskepxs3894040.jpg" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data is-center"><div class="data-item"><a href="/personal-blog/archives/"><div class="headline">Articles</div><div class="length-num">34</div></a></div><div class="data-item"><a href="/personal-blog/tags/"><div class="headline">Tags</div><div class="length-num">0</div></a></div><div class="data-item"><a href="/personal-blog/categories/"><div class="headline">Categories</div><div class="length-num">35</div></a></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/personal-blog/"><i class="fa-fw fas fa-home"></i><span> Home</span></a></div><div class="menus_item"><a class="site-page" href="/personal-blog/archives/"><i class="fa-fw fas fa-archive"></i><span> Archives</span></a></div><div class="menus_item"><a class="site-page" href="/personal-blog/categories/"><i class="fa-fw fas fa-folder-open"></i><span> Categories</span></a></div><div class="menus_item"><a class="site-page" href="/personal-blog/about/"><i class="fa-fw fas fa-heart"></i><span> About</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url('https://gitee.com/DreamcatcherDENG/drawing-bed/raw/master/img/wallhaven-e7ek7k.jpg')"><nav id="nav"><span id="blog_name"><a id="site-name" href="/personal-blog/">DreamcatcherDENG 的个人博客</a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> Search</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/personal-blog/"><i class="fa-fw fas fa-home"></i><span> Home</span></a></div><div class="menus_item"><a class="site-page" href="/personal-blog/archives/"><i class="fa-fw fas fa-archive"></i><span> Archives</span></a></div><div class="menus_item"><a class="site-page" href="/personal-blog/categories/"><i class="fa-fw fas fa-folder-open"></i><span> Categories</span></a></div><div class="menus_item"><a class="site-page" href="/personal-blog/about/"><i class="fa-fw fas fa-heart"></i><span> About</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">MySQL</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">Created</span><time class="post-meta-date-created" datetime="2022-07-13T09:38:00.957Z" title="Created 2022-07-13 17:38:00">2022-07-13</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">Updated</span><time class="post-meta-date-updated" datetime="2022-07-16T16:23:35.742Z" title="Updated 2022-07-17 00:23:35">2022-07-17</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/personal-blog/categories/MySQL/">MySQL</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-wordcount"><i class="far fa-file-word fa-fw post-meta-icon"></i><span class="post-meta-label">Word count:</span><span class="word-count">4.5k</span><span class="post-meta-separator">|</span><i class="far fa-clock fa-fw post-meta-icon"></i><span class="post-meta-label">Reading time:</span><span>19min</span></span><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="MySQL"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">Post View:</span><span id="busuanzi_value_page_pv"></span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><blockquote>
<p>本文介绍了MySQL的基本知识点，收录了SQL学习和练习网站，收录了学习MySQL过程中常见的问题及解决办法</p>
</blockquote>
<span id="more"></span>
<h1 id="MySQL学习教程"><a href="#MySQL学习教程" class="headerlink" title="MySQL学习教程"></a>MySQL学习教程</h1><ul>
<li><p><a target="_blank" rel="noopener" href="https://www.runoob.com/mysql/mysql-tutorial.html">菜鸟教程</a></p>
</li>
<li><p><a target="_blank" rel="noopener" href="https://www.bilibili.com/video/BV1Vy4y1z7EX?from=search&amp;seid=2131736679206311905&amp;spm_id_from=333.337.0.0">老杜带你学_mysql入门基础</a></p>
</li>
<li><p><a target="_blank" rel="noopener" href="https://dev.mysql.com/doc/refman/8.0/en/">MySQL官方参考手册</a></p>
</li>
<li><p><a target="_blank" rel="noopener" href="https://www.nowcoder.com/activity/oj?tab=1">牛客在线练习</a></p>
</li>
<li><p><a target="_blank" rel="noopener" href="https://leetcode-cn.com/problemset/database/">leetcode在线练习</a></p>
</li>
</ul>
<h1 id="MySQL常见问题"><a href="#MySQL常见问题" class="headerlink" title="MySQL常见问题"></a>MySQL常见问题</h1><h2 id="乱码问题"><a href="#乱码问题" class="headerlink" title="乱码问题"></a>乱码问题</h2><ul>
<li>查看字符编码<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span><span class="keyword">show</span> variables <span class="keyword">like</span> &quot;character%&quot;;</span><br></pre></td></tr></table></figure></li>
<li>设置字符编码<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">msyql&gt;set character_set_client  = utf8;</span><br><span class="line">msyql&gt;set character_set_connection  = utf8;</span><br><span class="line">msyql&gt;set character_set_database  = utf8;</span><br><span class="line">msyql&gt;set character_set_results  = utf8;</span><br><span class="line">msyql&gt;set character_set_server  = utf8;</span><br></pre></td></tr></table></figure>
<blockquote>
<p>注：这种方式设置的字符编码是临时的，MySQL重启后字符编码会恢复为默认值。</p>
</blockquote>
</li>
</ul>
<p>如果设置了字符编码存入数据库时还是乱码，则使用<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span><span class="keyword">set</span> names(&quot;UTF8&quot;)</span><br></pre></td></tr></table></figure><br>该语句等同于以下三条语句:<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">msyql<span class="operator">&gt;</span><span class="keyword">set</span> character_set_client  <span class="operator">=</span> utf8;</span><br><span class="line">msyql<span class="operator">&gt;</span><span class="keyword">set</span> character_set_results  <span class="operator">=</span> utf8;</span><br><span class="line">msyql<span class="operator">&gt;</span><span class="keyword">set</span> character_set_connection  <span class="operator">=</span> utf8;</span><br></pre></td></tr></table></figure></p>
<h2 id="连接问题"><a href="#连接问题" class="headerlink" title="连接问题"></a>连接问题</h2><ul>
<li>连接数据库</li>
</ul>
<p>登录指定服务器<code>192.168.*.*</code>:</p>
<p>mysql -h 服务器ip -u 用户名  -p密码</p>
<figure class="highlight cmd"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="built_in">cmd</span>&gt;mysql -h <span class="number">192</span>.<span class="number">168</span>.*.* -uroot -p</span><br><span class="line"><span class="function">password:******</span></span><br></pre></td></tr></table></figure>
<p>登录默认服务器localhost:</p>
<p>mysql -u用户名  -p密码:</p>
<figure class="highlight cmd"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="built_in">cmd</span>&gt;mysql -uroot -p</span><br><span class="line"><span class="function">password:******</span></span><br></pre></td></tr></table></figure>
<blockquote>
<p><code>-p</code>后面填写密码，但此密码填写时为明文，mysql不建议这么做，所以一般<code>-p</code>后面参数为空，提示<code>password:</code>后输入密码</p>
<ul>
<li>退出数据库<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span>exit</span><br><span class="line">mysql<span class="operator">&gt;</span>quit</span><br></pre></td></tr></table></figure>
<h2 id="修改密码"><a href="#修改密码" class="headerlink" title="修改密码"></a>修改密码</h2></li>
</ul>
</blockquote>
  <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span>update mysql.user <span class="keyword">set</span> authertication_string<span class="operator">=</span>password(&quot;password&quot;) <span class="keyword">where</span> <span class="keyword">user</span><span class="operator">=</span>&quot;username&quot; <span class="keyword">and</span> Host<span class="operator">=</span>&quot;hostip&quot;;</span><br></pre></td></tr></table></figure>
<h2 id="常量与系统变量"><a href="#常量与系统变量" class="headerlink" title="常量与系统变量"></a>常量与系统变量</h2><ul>
<li><strong>常量</strong><br>常量包含字符串、数字、日期、bool值、null值等</li>
<li><strong>系统变量</strong><br>系统变量可分为全局变量和会话变量，全局变量作用于MySQL服务器开启到结束用<code>@@变量名</code>表示，会话变量作用于会话连接开始到结束用<code>@变量名</code>表示</li>
<li><p>显示系统变量<br>显示以<code>a</code>开头的系统变量</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span><span class="keyword">show</span> variables <span class="keyword">like</span> &quot;a%&quot;</span><br></pre></td></tr></table></figure>
<h1 id="MySQL语法规范"><a href="#MySQL语法规范" class="headerlink" title="MySQL语法规范"></a>MySQL语法规范</h1></li>
<li><p>不区分大小写，建议关键字大写，表名、列名小写</p>
</li>
<li><p>每条命令最好用分号结尾</p>
</li>
<li><p>每条命令根据需要可缩进换行</p>
</li>
<li><p>注释</p>
<p>​    单行注释： <code>#注释文字</code></p>
<p>​    单行注释：<code>-- 注释文字（有空格）</code></p>
<p>​    多行注释：<code>/*注释文字*/</code></p>
</li>
</ul>
<h1 id="MySQL常用命令符"><a href="#MySQL常用命令符" class="headerlink" title="MySQL常用命令符"></a>MySQL常用命令符</h1><ol>
<li><p>查看当前数据库</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span><span class="keyword">show</span> databases;</span><br></pre></td></tr></table></figure>
</li>
<li><p>打开指定数据库</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span>use databasename;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查看当前所有表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span><span class="keyword">show</span> tables;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查看其他库的所有表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span><span class="keyword">show</span> tables <span class="keyword">from</span> datebasename;</span><br></pre></td></tr></table></figure>
<blockquote>
<p>注：当表名或库名为非正常字段时使用反引号将其包裹,比如当表名为某个关键字时，使用反引号将其包裹</p>
</blockquote>
</li>
<li><p>创建表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名(</span><br><span class="line">    列名 列类型，</span><br><span class="line">	列名 列类型，</span><br><span class="line">	...</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
</li>
</ol>
<ol>
<li><p>查看服务器版本</p>
<p>方式一:登录到mysql服务端</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">msyql<span class="operator">&gt;</span><span class="keyword">select</span> version();</span><br></pre></td></tr></table></figure>
<p>方式二:不登录mysql服务端</p>
<figure class="highlight cmd"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="built_in">cmd</span>&gt;mysql --version</span><br><span class="line"><span class="built_in">cmd</span>&gt;msyql -V</span><br></pre></td></tr></table></figure>
<blockquote>
<p>注：这里的V是大写V</p>
</blockquote>
</li>
<li><p>更改定界符</p>
<p>MySQL默认以输入定界符 <code>;</code>后，将<code>;</code>以前的作为命令执行单元换成，MySQL提供了更改命令执行符的命令<code>delimiter</code>。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span>delimiter <span class="operator">/</span><span class="operator">/</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> version()<span class="operator">/</span><span class="operator">/</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+</span></span><br><span class="line"><span class="operator">|</span> version() <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+</span></span><br><span class="line"><span class="operator">|</span> <span class="number">8.0</span><span class="number">.26</span>    <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+</span></span><br><span class="line"><span class="number">1</span> <span class="type">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br></pre></td></tr></table></figure>
</li>
</ol>
<h1 id="MySQL数据类型"><a href="#MySQL数据类型" class="headerlink" title="MySQL数据类型"></a>MySQL数据类型</h1><ol>
<li>字符串型</li>
</ol>
<ul>
<li>CHAR</li>
<li>VARCHAR</li>
<li>TEXT</li>
</ul>
<ol>
<li>数字整型</li>
</ol>
<ul>
<li>TINYINT</li>
<li>SMALLINT</li>
<li>MEDIUMINT</li>
<li>INT</li>
<li>BIGINT</li>
</ul>
<ol>
<li>日期和时间型</li>
</ol>
<ul>
<li>DATE</li>
<li>TIME</li>
<li>DATETIME</li>
<li>YEAR</li>
<li>TIMESTAMP</li>
</ul>
<ol>
<li>数字二进制型</li>
</ol>
<ul>
<li>TINYBLOB</li>
<li>TINYTEXT</li>
<li>BLOB</li>
<li>TEXT</li>
<li>MEDIUMBLOB</li>
<li>MEDIUMTEXT</li>
<li>LONGBLOB</li>
<li>LONGTEXT<br><details><summary>数据类型取值范围表</summary>



</li>
</ul>
<div class="table-container">
<table>
<thead>
<tr>
<th style="text-align:center">类型</th>
<th style="text-align:center">字节大小</th>
<th style="text-align:center">有符号范围</th>
<th style="text-align:center">无符号范围</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:center">SMALLINT</td>
<td style="text-align:center">2 bytes</td>
<td style="text-align:center">(-32 768，32 767)</td>
<td style="text-align:center">(0，65 535)</td>
</tr>
<tr>
<td style="text-align:center">MEDIUMINT</td>
<td style="text-align:center">3 bytes</td>
<td style="text-align:center">(-8 388 608，8 388 607)</td>
<td style="text-align:center">(0，16 777 215)</td>
</tr>
<tr>
<td style="text-align:center">INT或INTEGER</td>
<td style="text-align:center">4 bytes</td>
<td style="text-align:center">(-2 147 483 648，2 147 483 647)</td>
<td style="text-align:center">(0，4 294 967 295)</td>
</tr>
<tr>
<td style="text-align:center">BIGINT</td>
<td style="text-align:center">8 bytes</td>
<td style="text-align:center">(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)</td>
<td style="text-align:center">(0，18 446 744 073 709 551 615)</td>
</tr>
<tr>
<td style="text-align:center">FLOAT</td>
<td style="text-align:center">4 bytes</td>
<td style="text-align:center">(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)</td>
<td style="text-align:center">0，(1.175 494 351 E-38，3.402 823 466 E+38)</td>
</tr>
<tr>
<td style="text-align:center">DOUBLE</td>
<td style="text-align:center">8 bytes</td>
<td style="text-align:center">(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td>
<td style="text-align:center">0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td>
</tr>
<tr>
<td style="text-align:center">DATE</td>
<td style="text-align:center">3</td>
<td style="text-align:center">1000-01-01/9999-12-31</td>
<td style="text-align:center">YYYY-MM-DD</td>
</tr>
<tr>
<td style="text-align:center">TIME</td>
<td style="text-align:center">3</td>
<td style="text-align:center">‘-838:59:59’/‘838:59:59’</td>
<td style="text-align:center">HH:MM:SS</td>
</tr>
<tr>
<td style="text-align:center">YEAR</td>
<td style="text-align:center">1</td>
<td style="text-align:center">1901/2155</td>
<td style="text-align:center">YYYY</td>
</tr>
<tr>
<td style="text-align:center">DATETIME</td>
<td style="text-align:center">8</td>
<td style="text-align:center">1000-01-01 00:00:00/9999-12-31 23:59:59</td>
<td style="text-align:center">YYYY-MM-DD HH:MM:SS</td>
</tr>
<tr>
<td style="text-align:center">TIMESTAMP</td>
<td style="text-align:center">4</td>
<td style="text-align:center">1970-01-01 00:00:00/2038结束时间是第 <strong>2147483647</strong> 秒，北京时间 <strong>2038-1-19 11:14:07</strong>，格林尼治时间 2038年1月19日 凌晨 03:14:07</td>
<td style="text-align:center"></td>
</tr>
<tr>
<td style="text-align:center">CHAR</td>
<td style="text-align:center">0-255 bytes</td>
<td style="text-align:center"></td>
<td style="text-align:center">定长字符串</td>
</tr>
<tr>
<td style="text-align:center">VARCHAR</td>
<td style="text-align:center">0-65535 bytes</td>
<td style="text-align:center"></td>
<td style="text-align:center">变长字符串</td>
</tr>
<tr>
<td style="text-align:center">TINYBLOB</td>
<td style="text-align:center">0-255 bytes</td>
<td style="text-align:center"></td>
<td style="text-align:center">不超过 255 个字符的二进制字符串</td>
</tr>
<tr>
<td style="text-align:center">TINYTEXT</td>
<td style="text-align:center">0-255 bytes</td>
<td style="text-align:center"></td>
<td style="text-align:center">短文本字符串</td>
</tr>
<tr>
<td style="text-align:center">BLOB</td>
<td style="text-align:center">0-65 535 bytes</td>
<td style="text-align:center"></td>
<td style="text-align:center">二进制形式的长文本数据</td>
</tr>
<tr>
<td style="text-align:center">TEXT</td>
<td style="text-align:center">0-65 535 bytes</td>
<td style="text-align:center"></td>
<td style="text-align:center">长文本数据</td>
</tr>
<tr>
<td style="text-align:center">MEDIUMBLOB</td>
<td style="text-align:center">0-16 777 215 bytes</td>
<td style="text-align:center"></td>
<td style="text-align:center">二进制形式的中等长度文本数据</td>
</tr>
<tr>
<td style="text-align:center">MEDIUMTEXT</td>
<td style="text-align:center">0-16 777 215 bytes</td>
<td style="text-align:center"></td>
<td style="text-align:center">中等长度文本数据</td>
</tr>
</tbody>
</table>
</div>
<p>&lt;/details&gt;</p>
<h1 id="Navicat快捷键"><a href="#Navicat快捷键" class="headerlink" title="Navicat快捷键"></a>Navicat快捷键</h1><figure class="highlight markdown"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">打开查询窗口，新建查询窗口　　  Ctrl + Q</span><br><span class="line">打开一个mysql命令行窗口　　      F6</span><br><span class="line">运行查询窗口的SQL语句　　       Ctrl + R</span><br><span class="line">只运行选中的SQL语句                Ctrl + Shift + R</span><br><span class="line">运行光标右方或者已选择的语句　　F7</span><br><span class="line">注释SQL语句                             Ctrl +　/ 或者添加 #</span><br><span class="line">取消注释SQL语句                      Ctrl + Shift + /</span><br><span class="line">在查询窗口，打开一个新的查询窗口        Ctrl + N</span><br><span class="line">关闭当前窗口        Ctrl + W</span><br><span class="line">在表内容显示页面，快速切换到表设计页面　　Ctrl + D</span><br><span class="line">在表设计页面，快速切换到表内容显示页面　　Ctrl + O</span><br><span class="line">删除当前行　　   Ctrl + L　　</span><br><span class="line">复制本行　　      Ctrl + D</span><br><span class="line">查看历史日志　　Ctrl + H</span><br><span class="line">跳到整篇语句开头　　　Ctrl + Home</span><br><span class="line">跳到整篇语句结尾　　    Ctrl + End</span><br><span class="line">下一个Tab　　　           Ctrl + Tab</span><br><span class="line">打开一个查询窗口，并打开一个SQL文件覆盖当前窗口　　Ctrl + Q,Ctrl + O</span><br><span class="line">本行与上一行切换　　                                          Ctrl + T</span><br><span class="line">新建收藏夹（可把查询语句保存在收藏夹中）　　Ctrl + Shift + 1</span><br><span class="line">打开帮助文件　　F1　　</span><br></pre></td></tr></table></figure>
<h1 id="Mysql关键字"><a href="#Mysql关键字" class="headerlink" title="Mysql关键字"></a>Mysql关键字</h1><details>
<summary>MySQL关键字预览表</summary>

|       key       |                            function                            |
| :-------------: | :------------------------------------------------------------: |
|      where      |                            指定条件                            |
|       as        |                       起别名 （可省略）                        |
|      from       |                          指定数据来源                          |
|    distinct     |                   去重,查询结构中重复的结果                    |
|      like       |                            模糊查询                            |
|     between     |                          指定查询区间                          |
|       in        |                        指定查询结果列表                        |
| join ... on ... |                            连接查询                            |
|      inner      |               内连接，取出两表共有的部分作为主表               |
|      left       | 左连接，取出左表作为主表，右表与左表共有的部分作为从表加入主表 |
|      right      | 右连接，取出右表作为主表，左表与右表共有的部分作为从表加入主表 |
|    order by     |                              排序                              |
|      limit      |                    分页，所有语句的最后一个                    |

</details>

<h1 id="数据库语言"><a href="#数据库语言" class="headerlink" title="数据库语言"></a>数据库语言</h1><ul>
<li>DQL语言（Data Query Language）</li>
<li>DML语言（Data Manipulation Language）</li>
<li>DDL语言（Data Define Language)</li>
<li>TCL语言（Transaction Control Language)</li>
</ul>
<h2 id="DQL"><a href="#DQL" class="headerlink" title="DQL"></a>DQL</h2><p>数据查询语言专用于数据查询,查询列表可以是表中的字段，常量，表达式，函数</p>
<h3 id="1-查看有哪些数据库"><a href="#1-查看有哪些数据库" class="headerlink" title="1. 查看有哪些数据库"></a>1. 查看有哪些数据库</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span><span class="keyword">show</span> databases;</span><br></pre></td></tr></table></figure>
<h3 id="2-查询表中有哪些字段"><a href="#2-查询表中有哪些字段" class="headerlink" title="2. 查询表中有哪些字段"></a>2. 查询表中有哪些字段</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span><span class="keyword">show</span> columns <span class="keyword">from</span> tablename;</span><br></pre></td></tr></table></figure>
<h3 id="3-查询记录"><a href="#3-查询记录" class="headerlink" title="3. 查询记录"></a>3. 查询记录</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">msyql<span class="operator">&gt;</span><span class="keyword">select</span> key0[,key1,...] <span class="keyword">from</span> tablename [<span class="keyword">where</span> <span class="string">&#x27;conditioin&#x27;</span>];</span><br></pre></td></tr></table></figure>
<ul>
<li><p>查询单个字段所对应的结果</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> key0 <span class="keyword">from</span> tablename;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询多个字段所对应的结果</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> key0,key1,... <span class="keyword">from</span> tablename;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询所有字段</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> tablename;</span><br></pre></td></tr></table></figure>
</li>
</ul>
<ul>
<li><p>模糊查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> key <span class="keyword">from</span> tablename <span class="keyword">where</span> key <span class="keyword">like</span> <span class="string">&#x27;pattern&#x27;</span>;</span><br><span class="line"><span class="comment">--pattern是mysql的模糊查询匹配模式</span></span><br></pre></td></tr></table></figure>
<ul>
<li><p><code>%</code>    任意多个字符</p>
</li>
<li><p><code>_</code>    一个字符</p>
</li>
<li><p><code>__</code>    两个字</p>
</li>
<li><code>[ABCK]</code>  指定A,B,C,K任意一个字符</li>
<li><code>[A-Z]</code>  指定A-Z任意一个字符</li>
<li><code>[^A]</code>  指定非A字符</li>
<li>…</li>
</ul>
</li>
<li><p>联表查询</p>
<p><img src="http://124.221.123.212:8080/images/image-20210606165607454.png" alt="image-20210606165607454"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span><span class="keyword">select</span> t1.key1,t2.key1 </span><br><span class="line"><span class="operator">-</span><span class="operator">&gt;</span><span class="keyword">from</span> tablename1 <span class="keyword">as</span> t1 </span><br><span class="line"><span class="operator">-</span><span class="operator">&gt;</span><span class="keyword">inner</span> <span class="keyword">join</span> tablename2 <span class="keyword">as</span> t2 </span><br><span class="line"><span class="operator">-</span><span class="operator">&gt;</span><span class="keyword">on</span> t1.key1 <span class="operator">=</span>t2.key1</span><br></pre></td></tr></table></figure>
<p>自联结</p>
<p><img src="https://gitee.com/DreamcatcherDENG/drawing-bed/raw/master/img/image-20210606173841115.png" alt="image-20210606173841115"></p>
<p><img src="http://124.221.123.212:8080/images/image-20210606173917792.png" alt="image-20210606173917792"></p>
</li>
</ul>
<ul>
<li><p>例题</p>
<p><img src="http://124.221.123.212:8080/images/image-20210606175602962.png" alt="image-20210606175602962"></p>
</li>
</ul>
<ul>
<li>子查询<h3 id="4-使用查询语句做运算"><a href="#4-使用查询语句做运算" class="headerlink" title="4. 使用查询语句做运算"></a>4. 使用查询语句做运算</h3></li>
<li>算术运算<br><details><summary>算术运算符预览表</summary>

</li>
</ul>
<div class="table-container">
<table>
<thead>
<tr>
<th style="text-align:center">符号</th>
<th style="text-align:center">说明</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:center">+</td>
<td style="text-align:center">加法运算</td>
</tr>
<tr>
<td style="text-align:center">-</td>
<td style="text-align:center">减法运算</td>
</tr>
<tr>
<td style="text-align:center">/</td>
<td style="text-align:center">除法运算</td>
</tr>
<tr>
<td style="text-align:center">*</td>
<td style="text-align:center">乘法运算</td>
</tr>
<tr>
<td style="text-align:center">%</td>
<td style="text-align:center">求余运算</td>
</tr>
<tr>
<td style="text-align:center">mod</td>
<td style="text-align:center">求余运算</td>
</tr>
<tr>
<td style="text-align:center">div</td>
<td style="text-align:center">除法运算</td>
</tr>
</tbody>
</table>
</div>
<p>&lt;/details&gt;</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="number">1</span><span class="operator">+</span><span class="number">2</span><span class="operator">*</span><span class="number">3</span><span class="number">-4</span><span class="operator">/</span><span class="number">5</span>;</span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+</span></span><br><span class="line"><span class="operator">|</span> <span class="number">1</span><span class="operator">+</span><span class="number">2</span><span class="operator">*</span><span class="number">3</span><span class="number">-4</span><span class="operator">/</span><span class="number">5</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+</span></span><br><span class="line"><span class="operator">|</span>    <span class="number">6.2000</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+</span></span><br><span class="line"><span class="number">1</span> <span class="type">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="string">&#x27;abcdefe&#x27;</span> <span class="keyword">like</span> <span class="string">&#x27;a_&#x27;</span>;</span><br><span class="line"><span class="operator">+</span><span class="comment">---------------------+</span></span><br><span class="line"><span class="operator">|</span> <span class="string">&#x27;abcdefe&#x27;</span> <span class="keyword">like</span> <span class="string">&#x27;a_&#x27;</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">---------------------+</span></span><br><span class="line"><span class="operator">|</span>                   <span class="number">0</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">---------------------+</span></span><br><span class="line"><span class="number">1</span> <span class="type">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="string">&#x27;abcdefe&#x27;</span> <span class="keyword">like</span> <span class="string">&#x27;a%&#x27;</span>;</span><br><span class="line"><span class="operator">+</span><span class="comment">---------------------+</span></span><br><span class="line"><span class="operator">|</span> <span class="string">&#x27;abcdefe&#x27;</span> <span class="keyword">like</span> <span class="string">&#x27;a%&#x27;</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">---------------------+</span></span><br><span class="line"><span class="operator">|</span>                   <span class="number">1</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">---------------------+</span></span><br><span class="line"><span class="number">1</span> <span class="type">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="string">&#x27;abcdefe&#x27;</span> regexp <span class="string">&#x27;[*c]&#x27;</span>;</span><br><span class="line"><span class="operator">+</span><span class="comment">-------------------------+</span></span><br><span class="line"><span class="operator">|</span> <span class="string">&#x27;abcdefe&#x27;</span> regexp <span class="string">&#x27;[*c]&#x27;</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-------------------------+</span></span><br><span class="line"><span class="operator">|</span>                       <span class="number">1</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-------------------------+</span></span><br><span class="line"><span class="number">1</span> <span class="type">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="string">&#x27;abcdefe&#x27;</span> regexp <span class="string">&#x27;[*h]&#x27;</span>;</span><br><span class="line"><span class="operator">+</span><span class="comment">-------------------------+</span></span><br><span class="line"><span class="operator">|</span> <span class="string">&#x27;abcdefe&#x27;</span> regexp <span class="string">&#x27;[*h]&#x27;</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-------------------------+</span></span><br><span class="line"><span class="operator">|</span>                       <span class="number">0</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-------------------------+</span></span><br><span class="line"><span class="number">1</span> <span class="type">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<ul>
<li>比较运算<br><details><summary>比较运算符预览表</summary>

</li>
</ul>
<div class="table-container">
<table>
<thead>
<tr>
<th style="text-align:center">运算符</th>
<th style="text-align:center">说明</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:center">=</td>
<td style="text-align:center">等于</td>
</tr>
<tr>
<td style="text-align:center">&lt;</td>
<td style="text-align:center">小于</td>
</tr>
<tr>
<td style="text-align:center">…</td>
<td style="text-align:center">…</td>
</tr>
<tr>
<td style="text-align:center">is null</td>
<td style="text-align:center">判空</td>
</tr>
<tr>
<td style="text-align:center">is not null</td>
<td style="text-align:center">判非空</td>
</tr>
<tr>
<td style="text-align:center">between … and …</td>
<td style="text-align:center">区间比较</td>
</tr>
<tr>
<td style="text-align:center">in</td>
<td style="text-align:center">属于</td>
</tr>
<tr>
<td style="text-align:center">not in</td>
<td style="text-align:center">不属于</td>
</tr>
<tr>
<td style="text-align:center">like</td>
<td style="text-align:center">模式匹配</td>
</tr>
<tr>
<td style="text-align:center">not like</td>
<td style="text-align:center">模式匹配</td>
</tr>
<tr>
<td style="text-align:center">regexp</td>
<td style="text-align:center">正则匹配</td>
</tr>
</tbody>
</table>
</div>
<p>&lt;/details&gt;</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="string">&#x27;A&#x27;</span><span class="operator">&lt;</span> <span class="string">&#x27;B&#x27;</span>,<span class="number">1</span><span class="operator">+</span><span class="number">1</span><span class="operator">=</span><span class="number">2</span>,<span class="number">7</span><span class="operator">&lt;&gt;</span><span class="number">7</span>,<span class="number">7</span><span class="operator">!=</span><span class="number">7</span>,<span class="string">&#x27;a&#x27;</span><span class="operator">&lt;=</span><span class="string">&#x27;a&#x27;</span>,<span class="string">&#x27;a&#x27;</span> <span class="keyword">is</span> <span class="keyword">null</span>;</span><br><span class="line"><span class="operator">+</span><span class="comment">----------+-------+------+------+----------+-------------+</span></span><br><span class="line"><span class="operator">|</span> <span class="string">&#x27;A&#x27;</span><span class="operator">&lt;</span> <span class="string">&#x27;B&#x27;</span> <span class="operator">|</span> <span class="number">1</span><span class="operator">+</span><span class="number">1</span><span class="operator">=</span><span class="number">2</span> <span class="operator">|</span> <span class="number">7</span><span class="operator">&lt;&gt;</span><span class="number">7</span> <span class="operator">|</span> <span class="number">7</span><span class="operator">!=</span><span class="number">7</span> <span class="operator">|</span> <span class="string">&#x27;a&#x27;</span><span class="operator">&lt;=</span><span class="string">&#x27;a&#x27;</span> <span class="operator">|</span> <span class="string">&#x27;a&#x27;</span> <span class="keyword">is</span> <span class="keyword">null</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">----------+-------+------+------+----------+-------------+</span></span><br><span class="line"><span class="operator">|</span>        <span class="number">1</span> <span class="operator">|</span>     <span class="number">1</span> <span class="operator">|</span>    <span class="number">0</span> <span class="operator">|</span>    <span class="number">0</span> <span class="operator">|</span>        <span class="number">1</span> <span class="operator">|</span>           <span class="number">0</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">----------+-------+------+------+----------+-------------+</span></span><br><span class="line"><span class="number">1</span> <span class="type">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br></pre></td></tr></table></figure>
<ul>
<li>逻辑运算<br><details><summary>
逻辑运算符预览表
</summary>

</li>
</ul>
<div class="table-container">
<table>
<thead>
<tr>
<th style="text-align:center">符号</th>
<th style="text-align:center">说明</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:center">&amp;&amp;</td>
<td style="text-align:center">与</td>
</tr>
<tr>
<td style="text-align:center">\</td>
<td style="text-align:center">\</td>
<td></td>
<td>或</td>
</tr>
<tr>
<td style="text-align:center">or</td>
<td style="text-align:center">或</td>
</tr>
<tr>
<td style="text-align:center">!</td>
<td style="text-align:center">非</td>
</tr>
<tr>
<td style="text-align:center">not</td>
<td style="text-align:center">非</td>
</tr>
<tr>
<td style="text-align:center">nor</td>
<td style="text-align:center">异或</td>
</tr>
</tbody>
</table>
</div>
<p>&lt;/details&gt;</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="keyword">not</span>(<span class="string">&#x27;A&#x27;</span><span class="operator">=</span><span class="string">&#x27;B&#x27;</span>),(<span class="string">&#x27;c&#x27;</span><span class="operator">=</span>&quot;C&quot;),(<span class="string">&#x27;c&#x27;</span><span class="operator">&lt;</span><span class="string">&#x27;D&#x27;</span>),(<span class="number">1</span><span class="operator">=</span><span class="number">2</span>);</span><br><span class="line"><span class="operator">+</span><span class="comment">--------------+-----------+-----------+-------+</span></span><br><span class="line"><span class="operator">|</span> <span class="keyword">not</span>(<span class="string">&#x27;A&#x27;</span><span class="operator">=</span><span class="string">&#x27;B&#x27;</span>) <span class="operator">|</span> (<span class="string">&#x27;c&#x27;</span><span class="operator">=</span>&quot;C&quot;) <span class="operator">|</span> (<span class="string">&#x27;c&#x27;</span><span class="operator">&lt;</span><span class="string">&#x27;D&#x27;</span>) <span class="operator">|</span> (<span class="number">1</span><span class="operator">=</span><span class="number">2</span>) <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">--------------+-----------+-----------+-------+</span></span><br><span class="line"><span class="operator">|</span>            <span class="number">1</span> <span class="operator">|</span>         <span class="number">1</span> <span class="operator">|</span>         <span class="number">1</span> <span class="operator">|</span>     <span class="number">0</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">--------------+-----------+-----------+-------+</span></span><br><span class="line"><span class="number">1</span> <span class="type">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="keyword">not</span>(<span class="string">&#x27;A&#x27;</span><span class="operator">=</span><span class="string">&#x27;B&#x27;</span>),(<span class="string">&#x27;c&#x27;</span><span class="operator">=</span>&quot;C&quot;)<span class="keyword">and</span>(<span class="string">&#x27;c&#x27;</span><span class="operator">&lt;</span><span class="string">&#x27;D&#x27;</span>)<span class="keyword">or</span>(<span class="number">1</span><span class="operator">=</span><span class="number">2</span>);</span><br><span class="line"><span class="operator">+</span><span class="comment">--------------+------------------------------+</span></span><br><span class="line"><span class="operator">|</span> <span class="keyword">not</span>(<span class="string">&#x27;A&#x27;</span><span class="operator">=</span><span class="string">&#x27;B&#x27;</span>) <span class="operator">|</span> (<span class="string">&#x27;c&#x27;</span><span class="operator">=</span>&quot;C&quot;)<span class="keyword">and</span>(<span class="string">&#x27;c&#x27;</span><span class="operator">&lt;</span><span class="string">&#x27;D&#x27;</span>)<span class="keyword">or</span>(<span class="number">1</span><span class="operator">=</span><span class="number">2</span>) <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">--------------+------------------------------+</span></span><br><span class="line"><span class="operator">|</span>            <span class="number">1</span> <span class="operator">|</span>                            <span class="number">1</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">--------------+------------------------------+</span></span><br><span class="line"><span class="number">1</span> <span class="type">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br></pre></td></tr></table></figure>
<ul>
<li>位运算<br><details><summary>
位运算符预览表
</summary>

</li>
</ul>
<div class="table-container">
<table>
<thead>
<tr>
<th style="text-align:center">符号</th>
<th style="text-align:center">说明</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:center">&amp;</td>
<td style="text-align:center">按位与</td>
</tr>
<tr>
<td style="text-align:center">\</td>
<td style="text-align:center"></td>
<td>按位或</td>
</tr>
<tr>
<td style="text-align:center">~</td>
<td style="text-align:center">按位取反</td>
</tr>
<tr>
<td style="text-align:center">^</td>
<td style="text-align:center">按位异或</td>
</tr>
<tr>
<td style="text-align:center">&lt;&lt;</td>
<td style="text-align:center">左移</td>
</tr>
<tr>
<td style="text-align:center">&gt;&gt;</td>
<td style="text-align:center">右移</td>
</tr>
</tbody>
</table>
</div>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="number">1</span><span class="operator">&amp;</span><span class="number">2</span>,<span class="number">1</span><span class="operator">|</span><span class="number">2</span>,<span class="number">1</span><span class="operator">&gt;&gt;</span><span class="number">2</span>,<span class="number">1</span><span class="operator">&lt;&lt;</span><span class="number">2</span>,<span class="operator">~</span><span class="number">1</span>,<span class="number">2</span><span class="operator">^</span><span class="number">1</span>;</span><br><span class="line"><span class="operator">+</span><span class="comment">-----+-----+------+------+----------------------+-----+</span></span><br><span class="line"><span class="operator">|</span> <span class="number">1</span><span class="operator">&amp;</span><span class="number">2</span> <span class="operator">|</span> <span class="number">1</span><span class="operator">|</span><span class="number">2</span> <span class="operator">|</span> <span class="number">1</span><span class="operator">&gt;&gt;</span><span class="number">2</span> <span class="operator">|</span> <span class="number">1</span><span class="operator">&lt;&lt;</span><span class="number">2</span> <span class="operator">|</span> <span class="operator">~</span><span class="number">1</span>                   <span class="operator">|</span> <span class="number">2</span><span class="operator">^</span><span class="number">1</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----+-----+------+------+----------------------+-----+</span></span><br><span class="line"><span class="operator">|</span>   <span class="number">0</span> <span class="operator">|</span>   <span class="number">3</span> <span class="operator">|</span>    <span class="number">0</span> <span class="operator">|</span>    <span class="number">4</span> <span class="operator">|</span> <span class="number">18446744073709551614</span> <span class="operator">|</span>   <span class="number">3</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----+-----+------+------+----------------------+-----+</span></span><br><span class="line"><span class="number">1</span> <span class="type">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="number">2</span><span class="operator">&amp;</span><span class="number">3</span>,<span class="number">2</span><span class="operator">|</span><span class="number">3</span>,<span class="number">2</span><span class="operator">&gt;&gt;</span><span class="number">3</span>,<span class="number">2</span><span class="operator">&lt;&lt;</span><span class="number">3</span>,<span class="operator">~</span><span class="number">2</span>,<span class="number">2</span><span class="operator">^</span><span class="number">3</span>;</span><br><span class="line"><span class="operator">+</span><span class="comment">-----+-----+------+------+----------------------+-----+</span></span><br><span class="line"><span class="operator">|</span> <span class="number">2</span><span class="operator">&amp;</span><span class="number">3</span> <span class="operator">|</span> <span class="number">2</span><span class="operator">|</span><span class="number">3</span> <span class="operator">|</span> <span class="number">2</span><span class="operator">&gt;&gt;</span><span class="number">3</span> <span class="operator">|</span> <span class="number">2</span><span class="operator">&lt;&lt;</span><span class="number">3</span> <span class="operator">|</span> <span class="operator">~</span><span class="number">2</span>                   <span class="operator">|</span> <span class="number">2</span><span class="operator">^</span><span class="number">3</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----+-----+------+------+----------------------+-----+</span></span><br><span class="line"><span class="operator">|</span>   <span class="number">2</span> <span class="operator">|</span>   <span class="number">3</span> <span class="operator">|</span>    <span class="number">0</span> <span class="operator">|</span>   <span class="number">16</span> <span class="operator">|</span> <span class="number">18446744073709551613</span> <span class="operator">|</span>   <span class="number">1</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----+-----+------+------+----------------------+-----+</span></span><br><span class="line"><span class="number">1</span> <span class="type">row</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br></pre></td></tr></table></figure>
<p>&lt;/details&gt;</p>
<ul>
<li>运算优先级<br>| 优先级 |                    运算符                    |<br>| :——: | :—————————————————————: |<br>|   1    |                      !                       |<br>|   2    |                      ~                       |<br>|   3    |                      ^                       |<br>|   4    |                * / % div mod                 |<br>|   5    |                     + -                      |<br>|   6    |                    &gt;&gt; &lt;&lt;                     |<br>|   7    |                      &amp;                       |<br>|   8    |                      |                      |<br>|   9    | =,&lt;=&gt;,&lt;,&gt;=,&lt;=,&lt;&gt;,!=,&gt;,in,is,null,like,regexp |<br>|   10   |       between and,case,when,then,else        |<br>|   11   |                     not                      |<br>|   12   |                    &amp;&amp; and                    |<br>|   13   |                 || or xor                  |<br>|   14   |               :=     (赋值号)                |</li>
</ul>
<h2 id="DDL"><a href="#DDL" class="headerlink" title="DDL"></a>DDL</h2><p>数据定义语言设计对库和表的管理，包括创建、删除、修改</p>
<h3 id="数据库相关操作"><a href="#数据库相关操作" class="headerlink" title="数据库相关操作"></a>数据库相关操作</h3><ul>
<li>关键字</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">创建:<span class="keyword">create</span></span><br><span class="line">修改:<span class="keyword">alter</span></span><br><span class="line">删除:<span class="keyword">drop</span></span><br></pre></td></tr></table></figure>
<ul>
<li>创建数据库</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- sql server下创建</span></span><br><span class="line"><span class="keyword">CREATE</span> DATABASE EDUC ;</span><br><span class="line"><span class="keyword">ON</span> <span class="keyword">PRIMARY</span>(</span><br><span class="line">NAME <span class="operator">=</span>&quot;student_data&quot;,</span><br><span class="line">FILENAME<span class="operator">=</span><span class="string">&#x27;D:\29247\Documents\MYSQL\sql_data\student_data.mdf&#x27;</span>,</span><br><span class="line">SIZE <span class="operator">=</span> <span class="number">10</span>,</span><br><span class="line">MAXSIZE <span class="operator">=</span> <span class="number">50</span>,</span><br><span class="line">FILEGROWTH <span class="operator">=</span> <span class="number">5</span><span class="operator">%</span>)</span><br><span class="line">LOG <span class="keyword">ON</span> </span><br><span class="line">(NAME <span class="operator">=</span> &quot;student_log&quot;,FILENAME <span class="operator">=</span><span class="string">&#x27;D:\29247\Documents\MYSQL\sql_data\student_log.ldf&#x27;</span>,</span><br><span class="line">SIZE <span class="operator">=</span> <span class="number">2</span>MB,</span><br><span class="line">MAXSIZE <span class="operator">=</span> <span class="number">5</span>MB,</span><br><span class="line">FILEGROWTH <span class="operator">=</span> <span class="number">1</span>MB)</span><br></pre></td></tr></table></figure>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">-- Mysql下创建</span><br><span class="line">create database databasename;-- 以默认方式创建数据库</span><br><span class="line">create database databasename charset=utf8;-- 以指定方式创建数据库</span><br><span class="line">create database [if not exists] databasename;</span><br></pre></td></tr></table></figure>
<ul>
<li>查看怎样创建的数据库</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show create database [if exists] databasename;</span><br></pre></td></tr></table></figure>
<ul>
<li>删除数据库</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">drop database databasename;</span><br></pre></td></tr></table></figure>
<p>删除数据库时，可能遇到一下问题</p>
<p><img src="http://124.221.123.212:8080/images/image-20210505181112066.png" alt="image-20210505181112066"></p>
<h3 id="表的相关操作"><a href="#表的相关操作" class="headerlink" title="表的相关操作"></a>表的相关操作</h3><h4 id="一-表的设计"><a href="#一-表的设计" class="headerlink" title="一 .表的设计"></a>一 .表的设计</h4><ol>
<li><p>字段的属性</p>
<p>每张表都会存在一下5个字段</p>
<p><code>id</code> 主键</p>
<p><code>version</code> 乐观锁</p>
<p><code>is_delete</code>伪删除</p>
<p><code>gmt_create</code>创建时间</p>
<p><code>gmt_update</code>修改时间</p>
</li>
<li><p>表的创建</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">create table if not exists `tablename`(</span><br><span class="line">	&#x27;attr&#x27; type(width) not NULL AUTO_INCREMENT COMMENT &#x27;description&#x27;,</span><br><span class="line">    ‘attr1’ type(width) not NULL DEFAULT &#x27; &#x27; COMMENT &#x27; &#x27;,</span><br><span class="line">    字段	类型	是否为空	自动增长	备注，</span><br><span class="line">    ...</span><br><span class="line">    primary key (&#x27;attr&#x27;)</span><br><span class="line">)ENGINE=INNODB DEFAULT CHARSET=utf-8;</span><br></pre></td></tr></table></figure>
</li>
</ol>
<ol>
<li><p>表的修改</p>
<ul>
<li><p>修改表名</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">alter table &quot;表名&quot; rename as &#x27;表名&#x27;;</span><br></pre></td></tr></table></figure>
</li>
<li><p>添加字段</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">alter table &quot;表名&quot; add 字段名 字段属性 [字段属性]</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改表的字段</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">alter table &quot;表名&quot; modify 字段名 字段属性 --修改表的属性</span><br></pre></td></tr></table></figure>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">alter table &quot;表名&quot; change 字段名 字段名 字段属性 --表的字段重命名</span><br></pre></td></tr></table></figure>
</li>
<li><p>删除表的字段</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">alter table &quot;表名&quot; drop 字段名</span><br></pre></td></tr></table></figure>
</li>
<li><p>删除表</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">drop table &quot;tablename&quot;</span><br></pre></td></tr></table></figure>
</li>
<li><p>添加外键</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">alter table &quot;表名&quot;</span><br><span class="line">add constraint &#x27;FK_字段名&#x27; foreign key(`字段名`) references `字段名`;</span><br></pre></td></tr></table></figure>
<p>一般不建议在表中直接引用外键，推荐在应用层实现外键操作</p>
</li>
</ul>
</li>
</ol>
<h4 id="二-表数据的操作"><a href="#二-表数据的操作" class="headerlink" title="二.表数据的操作"></a>二.表数据的操作</h4><ul>
<li><p>插入</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">insert into tablename(attr0[,attr1,...]) values (value0[,value1,...]);</span><br></pre></td></tr></table></figure>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">insert into tablename values (value0,value1,...); </span><br><span class="line">--不写表的字段时values需要全部写出</span><br></pre></td></tr></table></figure>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">insert into tablename values (value0,value1,...),(value0,value1,...),...; </span><br><span class="line">--同时添加多条记录</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改</p>
<p>不指定修改条件会改动所有的字段 <del><code>update table set key=&#39;value&#39;;</code></del></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">update tablename set key1=&#x27;value1&#x27;,	key2=&#x27;value2&#x27; where &#x27;conditions&#x27;; </span><br></pre></td></tr></table></figure>
</li>
<li><p>删除</p>
<p><code>delete</code>和<code>truncat</code></p>
<p>相同点：都能删除表中的数据但不会删除表的结构</p>
<p>不同点：<code>delete</code>不会清空自增，<code>truncat</code>会清空自增</p>
<p><code>delete</code>删除的问题，使用<code>delete</code>删除数据库的现象：</p>
<ul>
<li>InnoDB    自增会从1开始（存在内存中）</li>
<li>MyISAM 自增会从上以自增量开始(存在文件中)</li>
</ul>
<p>不指定删除条件会删除所有的字段 <del><code>delete from tablename</code></del></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">delete from tablename where &#x27;conditions&#x27;;</span><br></pre></td></tr></table></figure>
<p>清空表中的数据</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">truncate tablename;</span><br></pre></td></tr></table></figure>
<h1 id="SQL编程"><a href="#SQL编程" class="headerlink" title="SQL编程"></a>SQL编程</h1></li>
</ul>
<h2 id="Mysql函数"><a href="#Mysql函数" class="headerlink" title="Mysql函数"></a><a target="_blank" rel="noopener" href="https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html">Mysql函数</a></h2><ul>
<li><p>数学函数<br>|        函数         |                 功能                  |<br>| :————————-: | :—————————————————-: |<br>|     abs(number)     |               求绝对值                |<br>|    sign(number)     | 返回number的符号 $s$,$s \in {-1,0,1}$ |<br>|     sin(number)     |                 正弦                  |<br>|     cos(number)     |                 余弦                  |<br>|     tan(number)     |                 正切                  |<br>|    asin(number)     |                反正弦                 |<br>|    acos(number)     |                反余弦                 |<br>|    atan(number)     |                反正切                 |<br>|   ceiling(number)   |               向上取整                |<br>|    floor(number)    |               向下取整                |<br>| round(number[,int]) |  默认四舍五入，int参数指定保留小数位  |<br>|   greatest(array)   |               求最大值                |<br>|    least(array)     |               求最小值                |<br>|   degree(radian)    |              弧度转角度               |<br>|   radians(degree)   |              角度转弧度               |<br>|        Pi()         |                 $\pi$                 |<br>|     exp(number)     |              $e^{value}$              |<br>|     log(number)     |           $\log<em>e(number)$            |<br>|    log10(number)    |          $log</em>{10}(number)$           |<br>|      pow(a,n)       |                 $a^n$                 |<br>|       sqrt(n)       |               $\sqrt n$               |<br>|       rand()        |      产生随机数$x$, $x \in(0,1)$      |</p>
</li>
<li><p>字符串函数<br>|             函数             |                         功能                          |<br>| :—————————————: | :—————————————————————————-: |<br>|       char_length(str)       |                   求字符串字符长度                    |<br>| concat(str1,str2[,str3,…]) |                      拼接字符串                       |<br>|         left(str,i)          |                     返回0~i的子串                     |<br>|         right(str,i)         |                  返回i~length的子串                   |<br>|         length(str)          |                   求字符串字节长度                    |<br>|          lower(str)          |                        转小写                         |<br>|          upper(str)          |                        转大写                         |<br>|          ltrim(str)          |                     删除起始空格                      |<br>|          rtrim(str)          |                     删除末尾空格                      |<br>|   replace(str1,str2,str3)    |                用str3替换str1中的str2                 |<br>|         reverse(str)         |                         反转                          |<br>|      substring(str,i,j)      | 取i~j的子窜$substr,substr = str[i;j]$其中 $ i,j$ 可取 |</p>
</li>
</ul>
<ul>
<li><p><a target="_blank" rel="noopener" href="https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html">聚合函数</a></p>
<p>|    函数    |     功能      |<br>| :————: | :—————-: |<br>| avg(field) |   求平均值    |<br>|  count(*)  | 求arr元素个数 |<br>| max(field) |   求最大值    |<br>| min(field) |   求最小值    |<br>| sum(field) |     求和      |</p>
</li>
<li><p><a target="_blank" rel="noopener" href="https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html">时间日期函数</a></p>
</li>
<li><p><a target="_blank" rel="noopener" href="https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html">加密函数</a></p>
<h3 id="函数的使用"><a href="#函数的使用" class="headerlink" title="函数的使用"></a>函数的使用</h3></li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">-&gt;select date_format(now(),&quot;%Y年%m月%d日 %H点%i分%s秒&quot;);</span><br><span class="line">	</span><br><span class="line">+------------------------------------------------+</span><br><span class="line">| date_format(now(),&quot;%Y年%m月%d日 %H点%i分%s秒&quot;) |</span><br><span class="line">+------------------------------------------------+</span><br><span class="line">| 2021年06月09日 11点07分10秒                    |</span><br><span class="line">+------------------------------------------------+</span><br><span class="line">1 row in set (0.01 sec)</span><br></pre></td></tr></table></figure>
<h2 id="Mysql自定义函数"><a href="#Mysql自定义函数" class="headerlink" title="Mysql自定义函数"></a>Mysql自定义函数</h2><ul>
<li>当函数体为复合类型时，应该使用<code>begin ... end</code>包裹复合结构</li>
<li>创建函数不能重名</li>
<li>符合结构中可以包含声明、循环、条件控制等语句</li>
</ul>
<h3 id="创建无参函数"><a href="#创建无参函数" class="headerlink" title="创建无参函数:"></a>创建无参函数:</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">-&gt;create function function_name()</span><br><span class="line">-&gt;returns return_type</span><br><span class="line">-&gt;return return_body;</span><br></pre></td></tr></table></figure>
<p><em><u>注：创建函数之前需要先信任函数创建者<code>set global log_bin_trust_function_creators=1</code>;</u></em></p>
<h3 id="例"><a href="#例" class="headerlink" title="例:"></a>例:</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">-&gt;create function mydate() </span><br><span class="line">-&gt;returns varchar(30)</span><br><span class="line">-&gt;return date_format(now(),&quot;%Y年%m月%d日 %H点%i分%s秒&quot;);</span><br></pre></td></tr></table></figure>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">-&gt; select mydate();</span><br><span class="line">+-----------------------------+</span><br><span class="line">| mydate()                    |</span><br><span class="line">+-----------------------------+</span><br><span class="line">| 2021年06月09日 11点14分30秒   |</span><br><span class="line">+-----------------------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<h3 id="创建有参函数"><a href="#创建有参函数" class="headerlink" title="创建有参函数"></a>创建有参函数</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">-&gt;create function function_name(num1 type,num2 type)</span><br><span class="line">-&gt;returns return_type</span><br><span class="line">-&gt;return return_body;</span><br></pre></td></tr></table></figure>
<h3 id="例-1"><a href="#例-1" class="headerlink" title="例:"></a>例:</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">-&gt;create function myAvg(num1 int,num2 int) </span><br><span class="line">-&gt;returns int</span><br><span class="line">-&gt;return (num1+num2)/2;</span><br></pre></td></tr></table></figure>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">-&gt;select myAvg(10,14);</span><br><span class="line">+--------------+</span><br><span class="line">| myAvg(10,14) |</span><br><span class="line">+--------------+</span><br><span class="line">|           12 |</span><br><span class="line">+--------------+</span><br><span class="line">1 row in set (0.01 sec)</span><br></pre></td></tr></table></figure>
<h3 id="删除函数："><a href="#删除函数：" class="headerlink" title="删除函数："></a>删除函数：</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">drop function [if exists] function_name;</span><br></pre></td></tr></table></figure>
<h2 id="Mysql自定义存储过程"><a href="#Mysql自定义存储过程" class="headerlink" title="Mysql自定义存储过程"></a>Mysql自定义存储过程</h2><p>存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。</p>
<p>存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。</p>
<p>存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。</p>
<h3 id="无参存储过程的创建"><a href="#无参存储过程的创建" class="headerlink" title="无参存储过程的创建"></a>无参存储过程的创建</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">-&gt;create procedure procedure_name()</span><br><span class="line">-&gt;procedure_body;</span><br></pre></td></tr></table></figure>
<p>例:</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">-&gt;create procedure show_version()</span><br><span class="line">-&gt;select version();</span><br></pre></td></tr></table></figure>
<p>调用:</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">-&gt;call show_version();</span><br><span class="line">+-----------+</span><br><span class="line">| version() |</span><br><span class="line">+-----------+</span><br><span class="line">| 8.0.23    |</span><br><span class="line">+-----------+</span><br><span class="line">1 row in set (0.01 sec)</span><br><span class="line"></span><br><span class="line">Query OK, 0 rows affected (0.01 sec)</span><br></pre></td></tr></table></figure>
<h3 id="有参数的存储过程的创建"><a href="#有参数的存储过程的创建" class="headerlink" title="有参数的存储过程的创建"></a>有参数的存储过程的创建</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">delimiter //</span><br></pre></td></tr></table></figure>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; create procedure delete_cou_by_cid(id int)</span><br><span class="line">-&gt; begin</span><br><span class="line">-&gt; delete from cou where cid=id;</span><br><span class="line">-&gt; end</span><br><span class="line">-&gt; //</span><br></pre></td></tr></table></figure>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">-&gt;call delete_cou_by_cid(1)//</span><br></pre></td></tr></table></figure>
<p><strong><em><u>创建存储过程时参数和字段不能是相同名称</u></em></strong></p>
<h3 id="带返回值的存储过程"><a href="#带返回值的存储过程" class="headerlink" title="带返回值的存储过程"></a>带返回值的存储过程</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; create procedure delete_cou_by_cid(in id int,out num int)</span><br><span class="line">-&gt; begin</span><br><span class="line">-&gt; delete from cou where cid=id into num;</span><br><span class="line">-&gt; end</span><br><span class="line">-&gt; //</span><br></pre></td></tr></table></figure>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">call delete_cou_by_cid(1,@num)//</span><br><span class="line">select @num//</span><br></pre></td></tr></table></figure>
<p><code>out</code>关键字指定存储过程的返回值</p>
<h3 id="条件语句"><a href="#条件语句" class="headerlink" title="条件语句:"></a>条件语句:</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">-&gt; DELIMITER //  </span><br><span class="line">-&gt; CREATE PROCEDURE proc2(IN parameter int)  </span><br><span class="line">-&gt; begin </span><br><span class="line">-&gt; declare var int;  </span><br><span class="line">-&gt; set var=parameter+1;  </span><br><span class="line">-&gt; if var=0 then </span><br><span class="line">-&gt; insert into t values(17);  </span><br><span class="line">-&gt; end if;  </span><br><span class="line">-&gt; if parameter=0 then </span><br><span class="line">-&gt; update t set s1=s1+1;  </span><br><span class="line">-&gt; else </span><br><span class="line">-&gt; update t set s1=s1+2;  </span><br><span class="line">-&gt; end if;  </span><br><span class="line">-&gt; end;  </span><br><span class="line">-&gt; //  </span><br><span class="line">mysql &gt; DELIMITER ;</span><br></pre></td></tr></table></figure>
<h3 id="case语句"><a href="#case语句" class="headerlink" title="case语句:"></a>case语句:</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br></pre></td><td class="code"><pre><span class="line">mysql &gt; DELIMITER //  </span><br><span class="line">mysql &gt; CREATE PROCEDURE proc3 (in parameter int)  </span><br><span class="line">-&gt; begin </span><br><span class="line">-&gt; declare var int;  </span><br><span class="line">-&gt; set var=parameter+1;  </span><br><span class="line">-&gt; case var  </span><br><span class="line">-&gt; when 0 then   </span><br><span class="line">-&gt; insert into t values(17);  </span><br><span class="line">-&gt; when 1 then   </span><br><span class="line">-&gt; insert into t values(18);  </span><br><span class="line">-&gt; else   </span><br><span class="line">-&gt; insert into t values(19);  </span><br><span class="line">-&gt; end case;  </span><br><span class="line">-&gt; end;  </span><br><span class="line">-&gt; //  </span><br><span class="line">mysql &gt; DELIMITER ; </span><br><span class="line">case</span><br><span class="line">when var=0 then</span><br><span class="line">insert into t values(30);</span><br><span class="line">when var&gt;0 then</span><br><span class="line">when var&lt;0 then</span><br><span class="line">else</span><br><span class="line">end case</span><br></pre></td></tr></table></figure>
<h3 id="循环语句"><a href="#循环语句" class="headerlink" title="循环语句:"></a>循环语句:</h3><ul>
<li><p><code>while ... end while</code>在执行操作前检查结果</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">mysql &gt; DELIMITER //  </span><br><span class="line">mysql &gt; CREATE PROCEDURE proc4()  </span><br><span class="line">-&gt; begin </span><br><span class="line">-&gt; declare var int;  </span><br><span class="line">-&gt; set var=0;  </span><br><span class="line">-&gt; while var&lt;6 do  </span><br><span class="line">-&gt; insert into t values(var);  </span><br><span class="line">-&gt; set var=var+1;  </span><br><span class="line">-&gt; end while;  </span><br><span class="line">-&gt; end;  </span><br><span class="line">-&gt; //  </span><br><span class="line">mysql &gt; DELIMITER ;</span><br></pre></td></tr></table></figure>
</li>
</ul>
<ul>
<li><p><code>repeat ... end repeat</code>在执行操作后检查结果</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">mysql &gt; DELIMITER //  </span><br><span class="line">mysql &gt; CREATE PROCEDURE proc5 ()  </span><br><span class="line">-&gt; begin   </span><br><span class="line">-&gt; declare v int;  </span><br><span class="line">-&gt; set v=0;  </span><br><span class="line">-&gt; repeat  </span><br><span class="line">-&gt; insert into t values(v);  </span><br><span class="line">-&gt; set v=v+1;  </span><br><span class="line">-&gt; until v&gt;=5  </span><br><span class="line">-&gt; end repeat;  </span><br><span class="line">-&gt; end;  </span><br><span class="line">-&gt; //  </span><br><span class="line">mysql &gt; DELIMITER ;</span><br></pre></td></tr></table></figure>
</li>
</ul>
<ul>
<li><p><code>loop ·····endloop</code>loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">mysql &gt; DELIMITER //  </span><br><span class="line">mysql &gt; CREATE PROCEDURE proc6 ()  </span><br><span class="line">-&gt; begin </span><br><span class="line">-&gt; declare v int;  </span><br><span class="line">-&gt; set v=0;  </span><br><span class="line">-&gt; LOOP_LABLE:loop  </span><br><span class="line">-&gt; insert into t values(v);  </span><br><span class="line">-&gt; set v=v+1;  </span><br><span class="line">-&gt; if v &gt;=5 then </span><br><span class="line">-&gt; leave LOOP_LABLE;  </span><br><span class="line">-&gt; end if;  </span><br><span class="line">-&gt; end loop;  </span><br><span class="line">-&gt; end;  </span><br><span class="line">-&gt; //  </span><br><span class="line">mysql &gt; DELIMITER ;</span><br></pre></td></tr></table></figure>
</li>
</ul>
<h3 id="存储过程与函数的比较"><a href="#存储过程与函数的比较" class="headerlink" title="存储过程与函数的比较"></a>存储过程与函数的比较</h3><p>存储过程的功能更复杂，函数的针对性更强</p>
<p>存储过程可以返回多个值，函数只能返回一个值</p>
<p>存储过程一般独立执行，函数可以作为其他SQL语句的一个组成部分</p>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">Author: </span><span class="post-copyright-info"><a href="mailto:undefined">dreamcatcherdeng</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">Link: </span><span class="post-copyright-info"><a href="https://dreamcatcherdeng.github.io/personal-blog/2022/07/13/MySQL/">https://dreamcatcherdeng.github.io/personal-blog/2022/07/13/MySQL/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">Copyright Notice: </span><span class="post-copyright-info">All articles in this blog are licensed under <a target="_blank" rel="noopener" href="https://creativecommons.org/licenses/by-nc-sa/4.0/">CC BY-NC-SA 4.0</a> unless stating additionally.</span></div></div><div class="tag_share"><div class="post-meta__tag-list"></div><div class="post_share"><div class="social-share" data-image="https://gitee.com/DreamcatcherDENG/drawing-bed/raw/master/img/wallhaven-e7ek7k.jpg" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/social-share.js/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/social-share.js/dist/js/social-share.min.js" defer></script></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/personal-blog/2022/07/13/Nodejs/"><img class="prev-cover" src="https://gitee.com/DreamcatcherDENG/drawing-bed/raw/master/img/wallhaven-0qxx54.jpg" onerror="onerror=null;src='/personal-blog/img/404.jpg'" alt="cover of previous post"><div class="pagination-info"><div class="label">Previous Post</div><div class="prev_info">JavaScript and NodeJs</div></div></a></div><div class="next-post pull-right"><a href="/personal-blog/2022/07/13/JAVA/"><img class="next-cover" src="https://gitee.com/DreamcatcherDENG/drawing-bed/raw/master/img/wallhaven-72rd8e.jpg" onerror="onerror=null;src='/personal-blog/img/404.jpg'" alt="cover of next post"><div class="pagination-info"><div class="label">Next Post</div><div class="next_info">JAVA</div></div></a></div></nav></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="is-center"><div class="avatar-img"><img src="https://n.sinaimg.cn/sinakd10100/452/w1080h972/20200416/93dc-iskepxs3894040.jpg" onerror="this.onerror=null;this.src='/personal-blog/img/friend_404.gif'" alt="avatar"/></div><div class="author-info__name">dreamcatcherdeng</div><div class="author-info__description"></div></div><div class="card-info-data is-center"><div class="card-info-data-item"><a href="/personal-blog/archives/"><div class="headline">Articles</div><div class="length-num">34</div></a></div><div class="card-info-data-item"><a href="/personal-blog/tags/"><div class="headline">Tags</div><div class="length-num">0</div></a></div><div class="card-info-data-item"><a href="/personal-blog/categories/"><div class="headline">Categories</div><div class="length-num">35</div></a></div></div><a id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/xxxxxx"><i class="fab fa-github"></i><span>Follow Me</span></a><div class="card-info-social-icons is-center"><a class="social-icon" href="https://github.com/DreamcatcherDENG" target="_blank" title="Github"><i class="fab fa-github"></i></a></div></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn fa-shake"></i><span>Announcement</span></div><div class="announcement_content">This is my Blog</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>Catalog</span><span class="toc-percentage"></span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#MySQL%E5%AD%A6%E4%B9%A0%E6%95%99%E7%A8%8B"><span class="toc-number">1.</span> <span class="toc-text">MySQL学习教程</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#MySQL%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98"><span class="toc-number">2.</span> <span class="toc-text">MySQL常见问题</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%B9%B1%E7%A0%81%E9%97%AE%E9%A2%98"><span class="toc-number">2.1.</span> <span class="toc-text">乱码问题</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%BF%9E%E6%8E%A5%E9%97%AE%E9%A2%98"><span class="toc-number">2.2.</span> <span class="toc-text">连接问题</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BF%AE%E6%94%B9%E5%AF%86%E7%A0%81"><span class="toc-number">2.3.</span> <span class="toc-text">修改密码</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%B8%B8%E9%87%8F%E4%B8%8E%E7%B3%BB%E7%BB%9F%E5%8F%98%E9%87%8F"><span class="toc-number">2.4.</span> <span class="toc-text">常量与系统变量</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#MySQL%E8%AF%AD%E6%B3%95%E8%A7%84%E8%8C%83"><span class="toc-number">3.</span> <span class="toc-text">MySQL语法规范</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#MySQL%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4%E7%AC%A6"><span class="toc-number">4.</span> <span class="toc-text">MySQL常用命令符</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#MySQL%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B"><span class="toc-number">5.</span> <span class="toc-text">MySQL数据类型</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#Navicat%E5%BF%AB%E6%8D%B7%E9%94%AE"><span class="toc-number">6.</span> <span class="toc-text">Navicat快捷键</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#Mysql%E5%85%B3%E9%94%AE%E5%AD%97"><span class="toc-number">7.</span> <span class="toc-text">Mysql关键字</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AF%AD%E8%A8%80"><span class="toc-number">8.</span> <span class="toc-text">数据库语言</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#DQL"><span class="toc-number">8.1.</span> <span class="toc-text">DQL</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E6%9F%A5%E7%9C%8B%E6%9C%89%E5%93%AA%E4%BA%9B%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">8.1.1.</span> <span class="toc-text">1. 查看有哪些数据库</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E6%9F%A5%E8%AF%A2%E8%A1%A8%E4%B8%AD%E6%9C%89%E5%93%AA%E4%BA%9B%E5%AD%97%E6%AE%B5"><span class="toc-number">8.1.2.</span> <span class="toc-text">2. 查询表中有哪些字段</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E6%9F%A5%E8%AF%A2%E8%AE%B0%E5%BD%95"><span class="toc-number">8.1.3.</span> <span class="toc-text">3. 查询记录</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-%E4%BD%BF%E7%94%A8%E6%9F%A5%E8%AF%A2%E8%AF%AD%E5%8F%A5%E5%81%9A%E8%BF%90%E7%AE%97"><span class="toc-number">8.1.4.</span> <span class="toc-text">4. 使用查询语句做运算</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#DDL"><span class="toc-number">8.2.</span> <span class="toc-text">DDL</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9B%B8%E5%85%B3%E6%93%8D%E4%BD%9C"><span class="toc-number">8.2.1.</span> <span class="toc-text">数据库相关操作</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%A1%A8%E7%9A%84%E7%9B%B8%E5%85%B3%E6%93%8D%E4%BD%9C"><span class="toc-number">8.2.2.</span> <span class="toc-text">表的相关操作</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%B8%80-%E8%A1%A8%E7%9A%84%E8%AE%BE%E8%AE%A1"><span class="toc-number">8.2.2.1.</span> <span class="toc-text">一 .表的设计</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BA%8C-%E8%A1%A8%E6%95%B0%E6%8D%AE%E7%9A%84%E6%93%8D%E4%BD%9C"><span class="toc-number">8.2.2.2.</span> <span class="toc-text">二.表数据的操作</span></a></li></ol></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#SQL%E7%BC%96%E7%A8%8B"><span class="toc-number">9.</span> <span class="toc-text">SQL编程</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#Mysql%E5%87%BD%E6%95%B0"><span class="toc-number">9.1.</span> <span class="toc-text">Mysql函数</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%87%BD%E6%95%B0%E7%9A%84%E4%BD%BF%E7%94%A8"><span class="toc-number">9.1.1.</span> <span class="toc-text">函数的使用</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#Mysql%E8%87%AA%E5%AE%9A%E4%B9%89%E5%87%BD%E6%95%B0"><span class="toc-number">9.2.</span> <span class="toc-text">Mysql自定义函数</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%9B%E5%BB%BA%E6%97%A0%E5%8F%82%E5%87%BD%E6%95%B0"><span class="toc-number">9.2.1.</span> <span class="toc-text">创建无参函数:</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BE%8B"><span class="toc-number">9.2.2.</span> <span class="toc-text">例:</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%9B%E5%BB%BA%E6%9C%89%E5%8F%82%E5%87%BD%E6%95%B0"><span class="toc-number">9.2.3.</span> <span class="toc-text">创建有参函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BE%8B-1"><span class="toc-number">9.2.4.</span> <span class="toc-text">例:</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%A0%E9%99%A4%E5%87%BD%E6%95%B0%EF%BC%9A"><span class="toc-number">9.2.5.</span> <span class="toc-text">删除函数：</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#Mysql%E8%87%AA%E5%AE%9A%E4%B9%89%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B"><span class="toc-number">9.3.</span> <span class="toc-text">Mysql自定义存储过程</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%97%A0%E5%8F%82%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E7%9A%84%E5%88%9B%E5%BB%BA"><span class="toc-number">9.3.1.</span> <span class="toc-text">无参存储过程的创建</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9C%89%E5%8F%82%E6%95%B0%E7%9A%84%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E7%9A%84%E5%88%9B%E5%BB%BA"><span class="toc-number">9.3.2.</span> <span class="toc-text">有参数的存储过程的创建</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%B8%A6%E8%BF%94%E5%9B%9E%E5%80%BC%E7%9A%84%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B"><span class="toc-number">9.3.3.</span> <span class="toc-text">带返回值的存储过程</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9D%A1%E4%BB%B6%E8%AF%AD%E5%8F%A5"><span class="toc-number">9.3.4.</span> <span class="toc-text">条件语句:</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#case%E8%AF%AD%E5%8F%A5"><span class="toc-number">9.3.5.</span> <span class="toc-text">case语句:</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%BE%AA%E7%8E%AF%E8%AF%AD%E5%8F%A5"><span class="toc-number">9.3.6.</span> <span class="toc-text">循环语句:</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E4%B8%8E%E5%87%BD%E6%95%B0%E7%9A%84%E6%AF%94%E8%BE%83"><span class="toc-number">9.3.7.</span> <span class="toc-text">存储过程与函数的比较</span></a></li></ol></li></ol></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>Recent Post</span></div><div class="aside-list"><div class="aside-list-item"><a class="thumbnail" href="/personal-blog/2022/07/13/RegularExpression/" title="正则表达式"><img src="https://gitee.com/DreamcatcherDENG/drawing-bed/raw/master/img/romance-2258599_960_720.jpg" onerror="this.onerror=null;this.src='/personal-blog/img/404.jpg'" alt="正则表达式"/></a><div class="content"><a class="title" href="/personal-blog/2022/07/13/RegularExpression/" title="正则表达式">正则表达式</a><time datetime="2022-07-13T09:38:01.002Z" title="Created 2022-07-13 17:38:01">2022-07-13</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/personal-blog/2022/07/13/Nodejs/" title="JavaScript and NodeJs"><img src="https://gitee.com/DreamcatcherDENG/drawing-bed/raw/master/img/wallhaven-0qxx54.jpg" onerror="this.onerror=null;this.src='/personal-blog/img/404.jpg'" alt="JavaScript and NodeJs"/></a><div class="content"><a class="title" href="/personal-blog/2022/07/13/Nodejs/" title="JavaScript and NodeJs">JavaScript and NodeJs</a><time datetime="2022-07-13T09:38:00.960Z" title="Created 2022-07-13 17:38:00">2022-07-13</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/personal-blog/2022/07/13/MySQL/" title="MySQL"><img src="https://gitee.com/DreamcatcherDENG/drawing-bed/raw/master/img/wallhaven-e7ek7k.jpg" onerror="this.onerror=null;this.src='/personal-blog/img/404.jpg'" alt="MySQL"/></a><div class="content"><a class="title" href="/personal-blog/2022/07/13/MySQL/" title="MySQL">MySQL</a><time datetime="2022-07-13T09:38:00.957Z" title="Created 2022-07-13 17:38:00">2022-07-13</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/personal-blog/2022/07/13/JAVA/" title="JAVA"><img src="https://gitee.com/DreamcatcherDENG/drawing-bed/raw/master/img/wallhaven-72rd8e.jpg" onerror="this.onerror=null;this.src='/personal-blog/img/404.jpg'" alt="JAVA"/></a><div class="content"><a class="title" href="/personal-blog/2022/07/13/JAVA/" title="JAVA">JAVA</a><time datetime="2022-07-13T09:38:00.948Z" title="Created 2022-07-13 17:38:00">2022-07-13</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/personal-blog/2022/07/13/ViewPager/" title="ViewPager"><img src="https://gitee.com/DreamcatcherDENG/drawing-bed/raw/master/img/wallhaven-0qxx54.jpg" onerror="this.onerror=null;this.src='/personal-blog/img/404.jpg'" alt="ViewPager"/></a><div class="content"><a class="title" href="/personal-blog/2022/07/13/ViewPager/" title="ViewPager">ViewPager</a><time datetime="2022-07-13T09:38:00.929Z" title="Created 2022-07-13 17:38:00">2022-07-13</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2022 By dreamcatcherdeng</div><div class="framework-info"><span>Framework </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>Theme </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="Read Mode"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="Toggle Between Light And Dark Mode"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="Toggle between single-column and double-column"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="Setting"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="Table Of Contents"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="Back To Top"><i class="fas fa-arrow-up"></i></button></div></div><div id="local-search"><div class="search-dialog"><nav class="search-nav"><span class="search-dialog-title">Local search</span><span id="loading-status"></span><button class="search-close-button"><i class="fas fa-times"></i></button></nav><div class="is-center" id="loading-database"><i class="fas fa-spinner fa-pulse"></i><span>  Loading the Database</span></div><div class="search-wrap"><div id="local-search-input"><div class="local-search-box"><input class="local-search-box--input" placeholder="Search for Posts" type="text"/></div></div><hr/><div id="local-search-results"></div></div></div><div id="search-mask"></div></div><div><script src="/personal-blog/js/utils.js"></script><script src="/personal-blog/js/main.js"></script><script src="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox.umd.js"></script><script src="/personal-blog/js/search/local-search.js"></script><div class="js-pjax"><script>if (!window.MathJax) {
  window.MathJax = {
    tex: {
      inlineMath: [ ['$','$'], ["\\(","\\)"]],
      tags: 'ams'
    },
    chtml: {
      scale: 1.2
    },
    options: {
      renderActions: {
        findScript: [10, doc => {
          for (const node of document.querySelectorAll('script[type^="math/tex"]')) {
            const display = !!node.type.match(/; *mode=display/)
            const math = new doc.options.MathItem(node.textContent, doc.inputJax[0], display)
            const text = document.createTextNode('')
            node.parentNode.replaceChild(text, node)
            math.start = {node: text, delim: '', n: 0}
            math.end = {node: text, delim: '', n: 0}
            doc.math.push(math)
          }
        }, ''],
        insertScript: [200, () => {
          document.querySelectorAll('mjx-container:not\([display]\)').forEach(node => {
            const target = node.parentNode
            if (target.nodeName.toLowerCase() === 'li') {
              target.parentNode.classList.add('has-jax')
            } else {
              target.classList.add('has-jax')
            }
          });
        }, '', false]
      }
    }
  }
  
  const script = document.createElement('script')
  script.src = 'https://cdn.jsdelivr.net/npm/mathjax@3/es5/tex-mml-chtml.js'
  script.id = 'MathJax-script'
  script.async = true
  document.head.appendChild(script)
} else {
  MathJax.startup.document.state(0)
  MathJax.texReset()
  MathJax.typeset()
}</script><link rel="stylesheet" type="text/css" href="https://cdn.jsdelivr.net/npm/katex@latest/dist/katex.min.css"><script src="https://cdn.jsdelivr.net/npm/katex@latest/dist/contrib/copy-tex.min.js"></script><link rel="stylesheet" type="text/css" href="https://cdn.jsdelivr.net/npm/katex@latest/dist/contrib/copy-tex.css"><script>(() => {
  document.querySelectorAll('#article-container span.katex-display').forEach(item => {
    btf.wrap(item, 'div', { class: 'katex-wrap'})
  })
})()</script></div><canvas class="fireworks" mobile="false"></canvas><script src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1/dist/fireworks.min.js"></script><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script></div><script src="/personal-blog/live2dw/lib/L2Dwidget.min.js?094cbace49a39548bed64abff5988b05"></script><script>L2Dwidget.init({"pluginModelPath":"assets/","model":{"jsonPath":"/personal-blog/live2dw/assets/shizuku.model.json"},"display":{"position":"left","width":250,"height":300,"hOffset":0,"vOffset":0,"opacity":0.1},"mobile":{"show":false},"log":false,"pluginJsPath":"lib/","pluginRootPath":"live2dw/","tagMode":false});</script></body></html>